package com.dy.yunying.biz.dao.hbdataclickhouse.impl;

import com.dy.yunying.api.req.raffle.RaffleDataReq;
import com.dy.yunying.api.resp.raffle.RaffleDataDetail;
import com.dy.yunying.api.resp.raffle.RaffleDataSummary;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.util.StopWatch;

import javax.annotation.Resource;
import java.util.List;

/**
 * @Description
 * @Author chengang
 * @Date 2022/11/9
 */
@Slf4j
@Component
@RequiredArgsConstructor
public class RaffleDataDao {

	private final YunYingProperties prop;

	@Resource(name = "hbdataclickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	/**
	 * 报表列表
	 * @param req
	 * @param page 导出不分页
	 * @return
	 */
	public List<RaffleDataDetail> list(RaffleDataReq req, boolean page) {
		final String sql = this.getListSql(req,page);
		log.debug("抽奖活动数据列表SQL: [\n{}]", sql);
		StopWatch watch = new StopWatch();
		watch.start("lotteryActivityDataSearch");
		final List<RaffleDataDetail> list = clickhouseTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(RaffleDataDetail.class));
		watch.stop();
		log.info("抽奖活动数据分页列表查询耗时: {}秒", watch.getTotalTimeMillis());
		return list;
	}

	public RaffleDataSummary summary(RaffleDataReq req) {
		final String sql = this.getSummarySql(req);
		log.debug("抽奖活动数据汇总SQL: [\n{}]", sql);
		StopWatch watch = new StopWatch();
		watch.start("lotteryActivityDataSummary");
		final RaffleDataSummary list = clickhouseTemplate.queryForObject(sql, new Object[]{}, new BeanPropertyRowMapper<>(RaffleDataSummary.class));
		watch.stop();
		log.info("抽奖活动数据汇总查询耗时: {}秒", watch.getTotalTimeMillis());
		return list;
	}

	public Long count(RaffleDataReq req) {

		final StringBuilder sql = new StringBuilder();

		sql.append("SELECT\n");
		sql.append("    COUNT(1)\n");
		sql.append("FROM\n");
		sql.append("    (\n");

		sql.append(this.getListSql(req,false));

		sql.append("    ) t\n");

		return clickhouseTemplate.queryForObject(sql.toString(), Long.class);
	}

	private String getListSql(RaffleDataReq req, Boolean page) {
		StringBuilder sqlBuilder = new StringBuilder();

		sqlBuilder.append("	 select  \n");
		sqlBuilder.append("	 	reportDate, \n");
		sqlBuilder.append("	 	activityId, \n");
		sqlBuilder.append("	 	activityName, \n");
		sqlBuilder.append("	 	pv, \n");
		sqlBuilder.append("	 	uv, \n");
		sqlBuilder.append("	 	ip, \n");
		sqlBuilder.append("	 	bindRoleNum,--绑定角色数 \n");
		sqlBuilder.append("	 	lotteryRoleNum,--抽奖角色数 \n");
		sqlBuilder.append("	 	exchangeRoleNum,--兑换角色数 \n");
		sqlBuilder.append("	 	if(uv > 0,ceil(round(totalLotterySum / uv,2),0), 0)  as perLotteryNum,--人均抽奖次数 \n");
		sqlBuilder.append("	 	taskRoleNum,--完成任务角色数 \n");
		sqlBuilder.append("	 	toDecimal64(round(totalRechargeAmountSumgt1 / IF(totalLotterySumgt1 > 0, totalLotterySumgt1, 1) ,2),2) as roleArppu--角色ARPPU \n");
		sqlBuilder.append("	 from ( \n");
		sqlBuilder.append("	 	select  \n");
		sqlBuilder.append("	 	    reportDate, \n");
		sqlBuilder.append("	 		activityId, \n");
		sqlBuilder.append("	 		activityName, \n");
		sqlBuilder.append("	 		COALESCE(sum(pv),0) as pv, \n");
		sqlBuilder.append("	 		COALESCE(sum(uv),0) as uv, \n");
		sqlBuilder.append("	 		COALESCE(sum(ip),0) as ip, \n");
		sqlBuilder.append("	 		COALESCE(sum(bindRoleNum),0) as bindRoleNum, \n");
		sqlBuilder.append("	 		COALESCE(sum(lotteryRoleNum),0) as lotteryRoleNum, \n");
		sqlBuilder.append("	 		COALESCE(sum(exchangeRoleNum),0) as exchangeRoleNum, \n");
		sqlBuilder.append("	 		COALESCE(sum(taskRoleNum),0) as taskRoleNum \n");
		sqlBuilder.append("	 	from ( \n");
		sqlBuilder.append("	 		select \n");
		sqlBuilder.append("	 			dx_part_date as reportDate, \n");
		sqlBuilder.append("	 			activity_id as activityId, \n");
		sqlBuilder.append("	 			activity_name as activityName, \n");
		sqlBuilder.append("	 			dx_event_name, \n");
		sqlBuilder.append("	 			if(dx_event_name = 'page_view', coalesce(count(*),0),0) as pv, \n");
		sqlBuilder.append("	 			if(dx_event_name = 'page_view', coalesce(count(DISTINCT dx_distinct_id),0),0) as uv, \n");
		sqlBuilder.append("	 			if(dx_event_name = 'page_view', coalesce(count(DISTINCT dx_ip),	0),0) as ip, \n");
		sqlBuilder.append("	 			if(dx_event_name = 'lottery_bind_role', coalesce(count(DISTINCT roleid), 0), 0) bindRoleNum, \n");
		sqlBuilder.append("	 			if(dx_event_name = 'lottery', coalesce(count(DISTINCT roleid), 0), 0) lotteryRoleNum, \n");
		sqlBuilder.append("	 			if(dx_event_name = 'redeem', coalesce(count(DISTINCT roleid), 0), 0) exchangeRoleNum, \n");
		sqlBuilder.append("	 			if(dx_event_name = 'lottery_task_complete', coalesce(count(DISTINCT roleid), 0), 0) taskRoleNum \n");
		sqlBuilder.append("	 		 \n");
		sqlBuilder.append("	 		FROM \n");
		sqlBuilder.append("	 			"+ prop.getDxEventTable() +" \n");
		sqlBuilder.append("	 		where \n");
		sqlBuilder.append("	 		  ((dx_event_name = 'page_view' and place = 'lottery') or dx_event_name = 'lottery_bind_role' or dx_event_name = 'lottery' or dx_event_name = 'redeem' or dx_event_name = 'lottery_task_complete')  \n");
		sqlBuilder.append("	 		  and activity_id != 0 \n");
		sqlBuilder.append("	 		  and activity_name != '' \n");

		sqlBuilder.append(this.getCondition(req));

		sqlBuilder.append("	 		group by \n");
		sqlBuilder.append("	 			dx_part_date, \n");
		sqlBuilder.append("	 			activity_id, \n");
		sqlBuilder.append("	 			activity_name, \n");
		sqlBuilder.append("	 			dx_event_name \n");
		sqlBuilder.append("	 		order by activity_id desc \n");
		sqlBuilder.append("	 		)  \n");
		sqlBuilder.append("	 group by  \n");
		sqlBuilder.append("	 reportDate, \n");
		sqlBuilder.append("	 activityId, \n");
		sqlBuilder.append("	 activityName \n");
		sqlBuilder.append("	 ) a \n");
		sqlBuilder.append("	  \n");
		sqlBuilder.append("	 FULL JOIN  \n");
		sqlBuilder.append("	  \n");
		sqlBuilder.append("	 ( \n");

		sqlBuilder.append("  select\n");
		sqlBuilder.append("      reportDate,\n");
		sqlBuilder.append("  	activityId,\n");
		sqlBuilder.append("  	activityName,\n");
		sqlBuilder.append("  	COALESCE(SUM(toInt64(roleTotalNum)),0) as totalLotterySum, --角色总的抽奖次数\n");
		sqlBuilder.append("  	COALESCE(SUM(toInt64(roleTotalNumgt1)),0) as totalLotterySumgt1, --抽奖次数＞1的角色数\n");
		sqlBuilder.append("  	COALESCE(SUM(roleTotalAmount),0) as totalRechargeAmountSum, -- 角色总的充值金额\n");
		sqlBuilder.append("  	COALESCE(SUM(if(toInt64(roleTotalNumgt1) = 1,roleTotalAmount,0)),0) as totalRechargeAmountSumgt1 -- 抽奖次数＞1的角色数的角色充值金额\n");
		sqlBuilder.append("  from (\n");
		sqlBuilder.append("  \n");
		sqlBuilder.append("  	select\n");
		sqlBuilder.append("      reportDate,\n");
		sqlBuilder.append("  	activityId,\n");
		sqlBuilder.append("  	activityName,\n");
		sqlBuilder.append("  	roleid,\n");
		sqlBuilder.append("  	COALESCE(SUM(toInt64(useLotteryNum)),0) as roleTotalNum, --角色已使用抽奖次数\n");
		sqlBuilder.append("  	SUM(if(toInt64(useLotteryNum) > 1,1,0)) as roleTotalNumgt1, --抽奖次数＞1是否大于1\n");
		sqlBuilder.append("  	SUM(totalRechargeAmount) as roleTotalAmount --角色充值总金额\n");
		sqlBuilder.append("  \n");
		sqlBuilder.append("  from (\n");
		sqlBuilder.append("  	select\n");
		sqlBuilder.append("  		dx_part_date as reportDate,\n");
		sqlBuilder.append("  		activity_id as activityId,\n");
		sqlBuilder.append("  		activity_name as activityName,\n");
		sqlBuilder.append("  		dx_event_name,\n");
		sqlBuilder.append("  		roleid,\n");
		sqlBuilder.append("  		if(dx_event_name = 'lottery', SUM(COALESCE(lottery_use_num,0)),0) as useLotteryNum,-- 每个角色的抽奖次数\n");
		sqlBuilder.append("  		if(dx_event_name = 'lottery_recharge',SUM(toDecimal64(recharge_amount,3)),0) as totalRechargeAmount -- 每个角色的充值总额\n");
		sqlBuilder.append("  	FROM\n");

		sqlBuilder.append("	 			"+ prop.getDxEventTable() +" \n");

		sqlBuilder.append("  	where\n");
		sqlBuilder.append("  	    (dx_event_name = 'lottery_recharge' or dx_event_name = 'lottery')\n");
		sqlBuilder.append("  		and activity_id != 0\n");
		sqlBuilder.append("  		and activity_name != ''\n");

		sqlBuilder.append(this.getCondition(req));

		sqlBuilder.append("  	group by\n");
		sqlBuilder.append("  		dx_part_date,\n");
		sqlBuilder.append("  		activity_id,\n");
		sqlBuilder.append("  		activity_name,\n");
		sqlBuilder.append("  		dx_event_name,\n");
		sqlBuilder.append("  		roleid\n");
		sqlBuilder.append("  	order by activity_id desc\n");
		sqlBuilder.append("  )\n");
		sqlBuilder.append("  group by reportDate,activityId,activityName,roleid\n");
		sqlBuilder.append("  )	\n");
		sqlBuilder.append("  group by reportDate,activityId,activityName\n");
		sqlBuilder.append("	 ) b  USING (reportDate,activityId,activityName)                \n");
		sqlBuilder.append("	 order by reportDate desc,activityId desc \n");
		if (page) {
			sqlBuilder.append("LIMIT\n");
			sqlBuilder.append("    ").append(req.offset()).append(", ").append(req.getSize()).append('\n');
		}

		return sqlBuilder.toString();
	}


	private String getSummarySql(RaffleDataReq req) {
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("  select \n");
		sqlBuilder.append("  	pv,\n");
		sqlBuilder.append("  	uv,\n");
		sqlBuilder.append("  	ip,\n");
		sqlBuilder.append("  	bindRoleNum,--绑定角色数\n");
		sqlBuilder.append("  	lotteryRoleNum,--抽奖角色数\n");
		sqlBuilder.append("  	exchangeRoleNum,--兑换角色数\n");
		sqlBuilder.append("  	if(uv > 0,ceil(round(totalLotterySum / uv,2),0), 0)  as perLotteryNum,--人均抽奖次数\n");
		sqlBuilder.append("  	taskRoleNum,--完成任务角色数\n");
		sqlBuilder.append("  	toDecimal64(round(totalRechargeAmountSumgt1 / IF(totalLotterySumgt1 > 0, totalLotterySumgt1, 1) ,2),2) as roleArppu--角色ARPPU\n");
		sqlBuilder.append("  from (\n");
		sqlBuilder.append("  	select \n");
		sqlBuilder.append("  	'groupKey' as groupKey,\n");
		sqlBuilder.append("  	COALESCE(sum(pv),0) as pv,\n");
		sqlBuilder.append("  	COALESCE(sum(uv),0) as uv,\n");
		sqlBuilder.append("  	COALESCE(sum(ip),0) as ip,\n");
		sqlBuilder.append("  	COALESCE(sum(bindRoleNum),0) as bindRoleNum,\n");
		sqlBuilder.append("  	COALESCE(sum(lotteryRoleNum),0) as lotteryRoleNum,\n");
		sqlBuilder.append("  	COALESCE(sum(exchangeRoleNum),0) as exchangeRoleNum,\n");
		sqlBuilder.append("  	COALESCE(sum(taskRoleNum),0) as taskRoleNum\n");
		sqlBuilder.append("  from \n");
		sqlBuilder.append("  (\n");
		sqlBuilder.append("  select\n");
		sqlBuilder.append("  	dx_event_name,\n");
		sqlBuilder.append("  	if(dx_event_name = 'page_view', coalesce(count(*),0),0) as pv,\n");
		sqlBuilder.append("  	if(dx_event_name = 'page_view', coalesce(count(DISTINCT dx_distinct_id),0),0) as uv,\n");
		sqlBuilder.append("  	if(dx_event_name = 'page_view', coalesce(count(DISTINCT dx_ip),	0),0) as ip,\n");
		sqlBuilder.append("  	if(dx_event_name = 'lottery_bind_role', coalesce(count(DISTINCT roleid), 0), 0) bindRoleNum,\n");
		sqlBuilder.append("  	if(dx_event_name = 'lottery', coalesce(count(DISTINCT roleid), 0), 0) lotteryRoleNum,\n");
		sqlBuilder.append("  	if(dx_event_name = 'redeem', coalesce(count(DISTINCT roleid), 0), 0) exchangeRoleNum,\n");
		sqlBuilder.append("  	if(dx_event_name = 'lottery_task_complete', coalesce(count(DISTINCT roleid), 0), 0) taskRoleNum\n");
		sqlBuilder.append("  \n");
		sqlBuilder.append("  FROM\n");
		sqlBuilder.append("	 			"+ prop.getDxEventTable() +" \n");
		sqlBuilder.append("  where\n");
		sqlBuilder.append("    ((dx_event_name = 'page_view' and place = 'lottery') or dx_event_name = 'lottery_bind_role' or dx_event_name = 'lottery' or dx_event_name = 'redeem' or dx_event_name = 'lottery_task_complete') \n");
		sqlBuilder.append("    and activity_id != 0\n");
		sqlBuilder.append("    and activity_name != ''\n");

		sqlBuilder.append(this.getCondition(req));

		sqlBuilder.append("  group by\n");
		sqlBuilder.append("  	dx_event_name\n");
		sqlBuilder.append("  ) \n");
		sqlBuilder.append("  ) a\n");
		sqlBuilder.append("  \n");
		sqlBuilder.append("  FULL JOIN \n");
		sqlBuilder.append("  \n");
		sqlBuilder.append("  (\n");

		sqlBuilder.append("  select\n");
		sqlBuilder.append("     'groupKey' as groupKey,\n");
		sqlBuilder.append("  	COALESCE(SUM(toInt64(roleTotalNum)),0) as totalLotterySum, --角色总的抽奖次数\n");
		sqlBuilder.append("  	COALESCE(SUM(toInt64(roleTotalNumgt1)),0) as totalLotterySumgt1, --抽奖次数＞1的角色数\n");
		sqlBuilder.append("  	COALESCE(SUM(roleTotalAmount),0) as totalRechargeAmountSum, -- 角色总的充值金额\n");
		sqlBuilder.append("  	COALESCE(SUM(if(toInt64(roleTotalNumgt1) = 1,roleTotalAmount,0)),0) as totalRechargeAmountSumgt1 -- 抽奖次数＞1的角色数的角色充值金额\n");
		sqlBuilder.append("  from (\n");
		sqlBuilder.append("  \n");
		sqlBuilder.append("  	select\n");
		sqlBuilder.append("  	roleid,\n");
		sqlBuilder.append("  	COALESCE(SUM(toInt64(useLotteryNum)),0) as roleTotalNum, --角色已使用抽奖次数\n");
		sqlBuilder.append("  	SUM(if(toInt64(useLotteryNum) > 1,1,0)) as roleTotalNumgt1, --抽奖次数＞1是否大于1\n");
		sqlBuilder.append("  	SUM(totalRechargeAmount) as roleTotalAmount --角色充值总金额\n");
		sqlBuilder.append("  \n");
		sqlBuilder.append("  from (\n");
		sqlBuilder.append("  	select\n");
		sqlBuilder.append("  		dx_event_name,\n");
		sqlBuilder.append("  		roleid,\n");
		sqlBuilder.append("  		if(dx_event_name = 'lottery', SUM(COALESCE(lottery_use_num,0)),0) as useLotteryNum,-- 每个角色的抽奖次数\n");
		sqlBuilder.append("  		if(dx_event_name = 'lottery_recharge',SUM(toDecimal64(recharge_amount,3)),0) as totalRechargeAmount -- 每个角色的充值总额\n");
		sqlBuilder.append("  	FROM\n");

		sqlBuilder.append("	 			"+ prop.getDxEventTable() +" \n");

		sqlBuilder.append("  	where\n");
		sqlBuilder.append("  	    (dx_event_name = 'lottery_recharge' or dx_event_name = 'lottery')\n");
		sqlBuilder.append("  		and activity_id != 0\n");
		sqlBuilder.append("  		and activity_name != ''\n");

		sqlBuilder.append(this.getCondition(req));

		sqlBuilder.append("  	group by\n");
		sqlBuilder.append("  		dx_event_name,\n");
		sqlBuilder.append("  		roleid\n");
		sqlBuilder.append("  )\n");
		sqlBuilder.append("  group by roleid\n");
		sqlBuilder.append("  )\n");

		sqlBuilder.append("  ) b  USING (groupKey)\n");

		return sqlBuilder.toString();
	}

	private String getCondition(RaffleDataReq req) {
		StringBuffer sql = new StringBuffer();
		if (StringUtils.isNotBlank(req.getActivityName())) {
			sql.append("and activity_name like '%").append(StringEscapeUtils.escapeSql(req.getActivityName())).append("%'");
		}
		if (StringUtils.isNotBlank(req.getActivityId())) {
			sql.append(" and activity_id = ").append(req.getActivityId());
		}
		if (StringUtils.isNotBlank(req.getStartTime())) {
			sql.append(" and dx_part_date >= '").append(req.getStartTime()).append("'");
		}
		if (StringUtils.isNotBlank(req.getEndTime())) {
			sql.append(" and dx_part_date <= '").append(req.getEndTime()).append("'").append(" \n");
		}
		return sql.toString();
	}


}
